using Microsoft.EntityFrameworkCore.Diagnostics;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using Serilog;
using System.Data.Common;
using System.Text;

namespace CoreManager.Core.Logging
{
    /// <summary>
    /// 自定义数据库日志记录器
    /// 提供更美观和易读的SQL日志输出
    /// </summary>
    public class DatabaseLogger : IDbCommandInterceptor
    {
        private readonly ILogger<DatabaseLogger> _logger;
        private static readonly object _lock = new object();

        public DatabaseLogger(ILogger<DatabaseLogger> logger)
        {
            _logger = logger;
        }

        /// <summary>
        /// 命令执行前记录
        /// </summary>
        public InterceptionResult<DbDataReader> ReaderExecuting(
            DbCommand command,
            CommandEventData eventData,
            InterceptionResult<DbDataReader> result)
        {
            LogSqlCommand(command, eventData, "QUERY");
            return result;
        }

        /// <summary>
        /// 命令执行后记录
        /// </summary>
        public ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(
            DbCommand command,
            CommandEventData eventData,
            InterceptionResult<DbDataReader> result,
            CancellationToken cancellationToken = default)
        {
            LogSqlCommand(command, eventData, "QUERY");
            return ValueTask.FromResult(result);
        }

        /// <summary>
        /// 非查询命令执行前记录
        /// </summary>
        public InterceptionResult<int> NonQueryExecuting(
            DbCommand command,
            CommandEventData eventData,
            InterceptionResult<int> result)
        {
            LogSqlCommand(command, eventData, "NON-QUERY");
            return result;
        }

        /// <summary>
        /// 非查询命令执行后记录
        /// </summary>
        public ValueTask<InterceptionResult<int>> NonQueryExecutingAsync(
            DbCommand command,
            CommandEventData eventData,
            InterceptionResult<int> result,
            CancellationToken cancellationToken = default)
        {
            LogSqlCommand(command, eventData, "NON-QUERY");
            return ValueTask.FromResult(result);
        }

        /// <summary>
        /// 标量命令执行前记录
        /// </summary>
        public InterceptionResult<object> ScalarExecuting(
            DbCommand command,
            CommandEventData eventData,
            InterceptionResult<object> result)
        {
            LogSqlCommand(command, eventData, "SCALAR");
            return result;
        }

        /// <summary>
        /// 标量命令执行后记录
        /// </summary>
        public ValueTask<InterceptionResult<object>> ScalarExecutingAsync(
            DbCommand command,
            CommandEventData eventData,
            InterceptionResult<object> result,
            CancellationToken cancellationToken = default)
        {
            LogSqlCommand(command, eventData, "SCALAR");
            return ValueTask.FromResult(result);
        }

        /// <summary>
        /// 记录SQL命令
        /// </summary>
        private void LogSqlCommand(DbCommand command, CommandEventData eventData, string commandType)
        {
            if (!_logger.IsEnabled(LogLevel.Information))
                return;

            lock (_lock)
            {
                var sql = FormatSqlCommand(command);
                // 修复：CommandEventData 没有 Duration 属性，改为直接使用 0 作为默认值
                var duration = 0.0;
                var parameters = FormatParameters(command);

                // 使用Serilog记录格式化的SQL日志
                Log.Information(
                    "🔍 [{CommandType}] {Duration}ms | {ConnectionId}\n" +
                    "📝 SQL: {Sql}\n" +
                    "📋 Parameters: {Parameters}",
                    commandType,
                    duration.ToString("F2"),
                    "Unknown", // 修复：DatabaseFacade 没有 GetConnectionId 方法，改为直接使用固定值
                    sql,
                    parameters
                );
            }
        }

        /// <summary>
        /// 格式化SQL命令
        /// </summary>
        private string FormatSqlCommand(DbCommand command)
        {
            var sql = command.CommandText;
            
            // 基本格式化
            sql = sql.Replace("SELECT", "\n  SELECT", StringComparison.OrdinalIgnoreCase);
            sql = sql.Replace("FROM", "\n  FROM", StringComparison.OrdinalIgnoreCase);
            sql = sql.Replace("WHERE", "\n  WHERE", StringComparison.OrdinalIgnoreCase);
            sql = sql.Replace("ORDER BY", "\n  ORDER BY", StringComparison.OrdinalIgnoreCase);
            sql = sql.Replace("GROUP BY", "\n  GROUP BY", StringComparison.OrdinalIgnoreCase);
            sql = sql.Replace("HAVING", "\n  HAVING", StringComparison.OrdinalIgnoreCase);
            sql = sql.Replace("LIMIT", "\n  LIMIT", StringComparison.OrdinalIgnoreCase);
            sql = sql.Replace("OFFSET", "\n  OFFSET", StringComparison.OrdinalIgnoreCase);
            sql = sql.Replace("JOIN", "\n  JOIN", StringComparison.OrdinalIgnoreCase);
            sql = sql.Replace("LEFT JOIN", "\n  LEFT JOIN", StringComparison.OrdinalIgnoreCase);
            sql = sql.Replace("RIGHT JOIN", "\n  RIGHT JOIN", StringComparison.OrdinalIgnoreCase);
            sql = sql.Replace("INNER JOIN", "\n  INNER JOIN", StringComparison.OrdinalIgnoreCase);
            sql = sql.Replace("UPDATE", "\n  UPDATE", StringComparison.OrdinalIgnoreCase);
            sql = sql.Replace("INSERT INTO", "\n  INSERT INTO", StringComparison.OrdinalIgnoreCase);
            sql = sql.Replace("DELETE FROM", "\n  DELETE FROM", StringComparison.OrdinalIgnoreCase);
            sql = sql.Replace("SET", "\n  SET", StringComparison.OrdinalIgnoreCase);
            sql = sql.Replace("VALUES", "\n  VALUES", StringComparison.OrdinalIgnoreCase);

            // 清理多余的空行
            sql = System.Text.RegularExpressions.Regex.Replace(sql, @"\n\s*\n", "\n");
            
            return sql.Trim();
        }

        /// <summary>
        /// 格式化参数
        /// </summary>
        private string FormatParameters(DbCommand command)
        {
            if (command.Parameters.Count == 0)
                return "无参数";

            var sb = new StringBuilder();
            for (int i = 0; i < command.Parameters.Count; i++)
            {
                var param = command.Parameters[i];
                var value = param.Value?.ToString() ?? "NULL";
                
                // 限制参数值长度
                if (value.Length > 100)
                {
                    value = value.Substring(0, 97) + "...";
                }

                sb.Append($"\n    {param.ParameterName} = {value} ({param.DbType})");
            }

            return sb.ToString();
        }
    }

    /// <summary>
    /// 数据库日志配置
    /// </summary>
    public static class DatabaseLoggingConfiguration
    {
        /// <summary>
        /// 配置数据库日志
        /// </summary>
        public static void ConfigureDatabaseLogging(this IServiceCollection services)
        {
            services.AddScoped<IDbCommandInterceptor, DatabaseLogger>();
        }
    }
}
